package Fuction;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;

public class Select {
    public void selectCourse(Connection connection)throws SQLException{
        String sql="select * from course ";
        PreparedStatement statement=connection.prepareStatement(sql);
        ResultSet n=statement.executeQuery();
        System.out.println("编号\t\t 名称\t已选人数\t容纳人数\t可修学分");
        while(n.next()){
            String id=n.getString("id");
            String courseName=n.getString("course_name");
            int nowSum=n.getInt("now_num");
            int sum=n.getInt("sum");
            double score=n.getDouble("course_score");
            System.out.print(id+"\t\t"+courseName+" \t "+nowSum+"\t\t "+sum+"\t\t "+score);
            System.out.println("");
        }
        System.out.println();
        statement.close();
    }

    public void selectStudent(Connection connection)throws SQLException{
        String sql="select * from student ";
        PreparedStatement statement=connection.prepareStatement(sql);
        ResultSet n=statement.executeQuery();
        while(n.next()){
            System.out.println("学号\t 姓名\t年龄\t\t学分");
            String id=n.getString("id");
            String studentName=n.getString("name");
            int age=n.getInt("age");
            double score=n.getDouble("sum_score");
            System.out.print(id+"\t "+studentName+" \t "+age+"\t\t"+score);
            System.out.println("");

            String sql2="select * from score where student_name=? order by course_score";
            PreparedStatement statement2=connection.prepareStatement(sql2);
            statement2.setString(1,studentName);
            ResultSet n2=statement2.executeQuery();
            System.out.println(studentName+"的各科成绩如下");
            System.out.println("课程名称\t 总评\t获得学分\t通过情况");
            while(n2.next()){
                String courseName=n2.getString("course_name");
                double courseScore=n2.getDouble("course_score");
                double courseCredit=n2.getDouble("credit");
                String condition=n2.getString("course_condition");
                System.out.print(courseName+" \t "+courseScore+"\t "+courseCredit+"\t "+condition);
                System.out.println("");
            }
            System.out.println("-------------------------------------------");
        }

        System.out.println();
        statement.close();
    }

    public void selectFail(Connection connection)throws SQLException{
        String sql="select * from student where test_condition='挂科' ";
        PreparedStatement statement=connection.prepareStatement(sql);
        ResultSet n=statement.executeQuery();
        System.out.println("已成功生成补考通知并发送到各位同学邮箱");
        System.out.println("");
        while(n.next()){
            String studentName=n.getString("name");
            String sql2="select * from score where student_name=? and course_condition='挂科'";
            PreparedStatement statement2=connection.prepareStatement(sql2);
            statement2.setString(1,studentName);
            ResultSet n2=statement2.executeQuery();
            System.out.println("---------------------------------------------------------------");
            System.out.println("亲爱的"+studentName+"同学：");
            System.out.println("    你好，在本学期你存在未合格的科目，在此通知你相关科目的补考时间如下");
            System.out.println("补考科目\t 补考时间");
            while(n2.next()){
                String courseName=n2.getString("course_name");
                String time=n2.getString("re_time");
                System.out.println(courseName+"\t "+time);
            }
            System.out.println("---------------------------------------------------------------");
        }
        System.out.println();
        statement.close();
    }
}
